# -*- coding:utf-8 -*-


import json
import re


from lib.container import Container


class DB(object):

    query_obj_ = {
        "column": [],
        "table_name": [],
        "where": [],
        "bind": {},
        "group_by": [],
        "order_by": [],
        "skip": 0,
        "take": "-1",
    }
    query_obj = {}

    def __init__(self):
        # assert isinstance(config, dict), "请以字典类型的格式传入！"
        # if not self._conn:
        #     self.connection()
        pass

    @classmethod
    def table(cls, name: str):
        return cls()._table(name)

    @staticmethod
    def connenction(driver: str):
        pass

    table_name = ""

    def _table(self, name: str):
        """_summary_
        每次都将查询对象初始化
        Args:
            name (str): _description_

        Returns:
            _type_: _description_
        """
        self.query_obj = json.loads(json.dumps(self.query_obj_))
        self.query_obj["table_name"].append(name.strip())
        return self

    def join(self, table: str, raw: str):
        self.query_obj["inner"].append(f" inner join {table} on {raw}")
        return self

    def left(self, table: str, raw: str):
        self.query_obj["left"].append(f" left join {table} on {raw} ")
        return self

    def right(self, table: str, raw: str):
        self.query_obj["right"].append(f" right join {table} on {raw} ")
        return self

    def where(
        self,
        column: str = "",
        operator=None,
        value=None,
        bind: bool = True,
        raw: str = "",
        *args,
        **kwargs,
    ):
        """or用raw参数如(a=1 or b=2)加上括号

        Args:
            column (str, optional): _description_. Defaults to "".
            operator (str, optional): _description_. Defaults to "".
            value (_type_, optional): _description_. Defaults to None.
            raw (str, optional): _description_. Defaults to "".

        Returns:
            _type_: _description_
        """

        column = column.strip()
        if operator:
            operator = str(operator)

        if value:
            value = str(value)

        if column != "" and operator != "" and not value and bind:
            ## 参数绑定
            bind_column = f"{column}_eq"
            self.query_obj["where"].append(f"{column} = :{bind_column} ")
            self.query_obj["bind"][bind_column] = operator

        if column != "" and operator != "" and not value and not bind:
            self.query_obj["where"].append(f"{column} = {operator} ")

        if column != "" and operator != "" and value and bind:
            operator = operator.strip()
            ## 参数绑定
            bind_column = f"{column}"
            if operator == ">":
                bind_column = f"{column}_gt"
            if operator == ">=":
                bind_column = f"{column}_gte"
            if operator == "=":
                bind_column = f"{column}_eq"
            if operator == "<":
                bind_column = f"{column}_lt"
            if operator == "<=":
                bind_column = f"{column}_lte"
            self.query_obj["where"].append(f"{column} {operator} :{bind_column}")
            self.query_obj["bind"][bind_column] = value

        if column != "" and operator != "" and value and not bind:
            operator = operator.strip()
            self.query_obj["where"].append(f"{column} {operator} {value}")

        if raw != "":
            self.query_obj["where"].append(raw)
        bind = kwargs.get("bind")
        if bind and isinstance(bind, dict):
            self.query_obj["bind"].update(bind)
        return self

    def group_by(self, column):
        self.query_obj["group_by"].append(column)
        return self

    def order_by(self, column: str = "", direct: str = "asc"):
        self.query_obj["order_by"].append(f"{column} {direct}")
        return self

    def take(self, num: int):
        self.query_obj["take"] = num
        if num == 0:
            del self.query_obj["take"]
        return self

    def skip(self, num: int = 0):
        self.query_obj["skip"] = num
        return self

    def init_column(self):
        self.query_obj["column"] = []
        return self

    def select(self, column="*"):
        self.query_obj["column"].append(column)
        return self

    def appendField(self, column):
        self.query_obj["column"].append(column)
        return self

    def first(self):
        self.take(1)
        sql_obj = self.pre_select_sql()
        sql = sql_obj.get("sql")
        bind_value = sql_obj.get("bind_value")
        list = None
        self.curosr().execute(sql, bind_value)
        list = self.curosr().fetchone()
        return list

    def get(self):
        sql_obj = self.pre_select_sql()
        sql = sql_obj.get("sql")
        bind_value = sql_obj.get("bind_value")
        print(sql, bind_value)
        list = None
        self.curosr().execute(sql, bind_value)
        list = self.curosr().fetchall()
        """ 
        会自动关闭
        with self.curosr() as cursor:
            cursor.execute(sql, bind_value)
            list = cursor.fetchall() """
        if not list:
            list = []
        return list

    def count(self, column="1"):
        self.select(("count(%s) as Number" % column))
        self.take(1)
        sql_obj = self.pre_select_sql()
        sql = sql_obj.get("sql")
        bind_value = sql_obj.get("bind_value")
        list = None
        self.curosr().execute(sql, bind_value)
        list = self.curosr().fetchone()
        self.init_column()
        self.take(0)
        return list.get("Number", 0)

    def pluck(self, column: str):
        pass

    def value(self, column: str):
        pass

    def to_sql(self, show_value: bool = False):
        res = self.assemble()
        bind = res.get("bind", {})
        sql = res.get("sql", "")
        for k in res.get("bind", {}):
            v = bind.get(k, "")
            if show_value:
                sql = sql.replace(f":{k}", f"'{v}'", -1)
        res["sql"] = sql
        return res

    def pre_select_sql(self):
        res = self.assemble()
        bind = res.get("bind", {})
        sql = res.get("sql", "")
        bind_value = []
        for k in res.get("bind", {}):
            v = bind.get(k, "")
            sql = sql.replace(f":{k}", "%s", -1)
            bind_value.append(f"{v}")
        res["sql"] = sql
        res["bind_value"] = bind_value
        return res

    def assemble(self):
        """sql组装

        Returns:
            _type_: _description_
        """
        column_list = self.query_obj.get("column", ["*"])
        if column_list == []:
            column_list = ["*"]
        column = ",".join(column_list)
        table_name_list = self.query_obj.get("table_name", [])
        table_name = ",".join(table_name_list)
        inner_list = self.query_obj.get("inner", [])
        inner = " ".join(inner_list)
        left_list = self.query_obj.get("left", [])
        left = " ".join(left_list)
        right_list = self.query_obj.get("right", [])
        right = " ".join(right_list)
        where_list = self.query_obj.get("where", [])
        where = " and ".join(where_list)
        group_by_list = self.query_obj.get("group_by", [])
        group_by = ",".join(group_by_list)
        order_by_list = self.query_obj.get("order_by", [])
        order_by = ",".join(order_by_list)
        sql = f"select {column} from {table_name}"
        if inner != "":
            sql = sql + " " + inner
        if left != "":
            sql = sql + " " + left
        if right != "":
            sql = sql + " " + right

        if where != "":
            sql = sql + " where " + where
        if group_by != "":
            sql = sql + " group by " + group_by
        if order_by != "":
            sql = sql + " order by " + order_by
        take = str(self.query_obj.get("take", -1))
        skip = str(self.query_obj.get("skip", 0))
        if take != "-1":
            sql = sql + " limit " + ",".join([skip, take])
        # res = re.search(r":(?P<bind>[a-z A-Z 0-9]*)", sql,  re.M|re.I)
        pattern = re.compile(r":(?P<bind>[a-z|A-Z|0-9|_]*)")
        bind_param_list = pattern.findall(sql)
        bind = {}
        for k in bind_param_list:
            bind[k] = self.query_obj["bind"].get(k, "")
        return {"sql": sql, "bind": bind}

    def sql(self, string: str):
        pass

    def excute(self, sql: str):
        pass

    def pre_op_sql(self):
        where_list = self.query_obj.get("where", [])
        where = " and ".join(where_list)
        sql = ""
        if where != "":
            sql = sql + " where " + where
        pattern = re.compile(r":(?P<bind>[a-z|A-Z|0-9|_]*)")
        bind_param_list = pattern.findall(sql)
        bind = {}
        for k in bind_param_list:
            bind[k] = self.query_obj["bind"].get(k, "")
        return {"sql": sql, "bind": bind}

    def to_update_sql(self, data: dict, show_value: bool = False):
        table_name_list = self.query_obj.get("table_name", [])
        table_name = ",".join(table_name_list)
        sql_obj = self.pre_op_sql()
        append_sql = sql_obj.get("sql", "")
        bind = sql_obj.get("bind", {})
        set_data_list = []
        for k in data:
            v = data[k]
            bind_column = f"{k}_set"
            set_data_list.append(f"{k}=:{bind_column}")
            bind[bind_column] = v
        set_data = ",".join(set_data_list)
        sql = f"update {table_name} set {set_data} {append_sql}"
        pattern = re.compile(r":(?P<bind>[a-z|A-Z|0-9|_]*)")
        bind_param_list = pattern.findall(sql)
        bind_value = []
        for k in bind_param_list:
            v = bind.get(k, "")
            bind_value.append(f"{v}")
            if show_value:
                sql = sql.replace(f":{k}", f"{v}", -1)
                continue
            sql = sql.replace(f":{k}", "%s", -1)
        return {"sql": sql, "bind_value": bind_value, "bind": bind}

    def update(self, data: dict):
        ## self.curosr().execute(sql, bind_value)
        try:
            sql_obj = self.to_update_sql(data)
            sql = sql_obj.get("sql", "")
            bind_value = sql_obj.get("bind_value", "")
            self.curosr().execute(sql, bind_value)
            self.connection().commit()
            return True
        except Exception as e:
            # 发生错误时回滚
            self.connection().rollback()
        return False

    def to_delete_sql(self, show_value: bool = False):
        table_name_list = self.query_obj.get("table_name", [])
        table_name = ",".join(table_name_list)
        sql_obj = self.pre_op_sql()
        append_sql = sql_obj.get("sql", "")
        bind = sql_obj.get("bind", {})

        sql = f"delete from {table_name} {append_sql}"
        pattern = re.compile(r":(?P<bind>[a-z|A-Z|0-9|_]*)")
        bind_param_list = pattern.findall(sql)
        bind_value = []
        for k in bind_param_list:
            v = bind.get(k, "")
            bind_value.append(f"{v}")
            if show_value:
                sql = sql.replace(f":{k}", f"{v}", -1)
                continue
            sql = sql.replace(f":{k}", "%s", -1)
        return {"sql": sql, "bind_value": bind_value, "bind": bind}

    def delete(self):
        try:
            sql_obj = self.to_delete_sql()
            sql = sql_obj.get("sql", "")
            bind_value = sql_obj.get("bind_value", "")
            self.curosr().execute(sql, bind_value)
            self.connection().commit()
            return True
        except Exception as e:
            # 发生错误时回滚
            self.connection().rollback()
        return False

    def insert(self, data=None):
        try:
            table_name_list = self.query_obj.get("table_name", [])
            table_name = ",".join(table_name_list)
            columns_list = []
            value_list = []
            bind_value = []
            if isinstance(data, dict):
                for k in data:
                    columns_list.append(k)
                    v = str(data[k])
                    value_list.append("%s")
                    bind_value.append(v)

            if isinstance(data, list):
                columns_dict = {}
                for k in data:
                    val = data[k]
                    val_list = []
                    for c in val:
                        columns_dict[c] = c
                        v = str(data[c])
                        value_list.append("%s")
                        bind_value.append(v)
                    value_list.append(",".join(val_list))
                for k in columns_dict:
                    columns_list.append(k)
            columns = ",".join(columns_list)
            if isinstance(data, dict):
                values = ",".join(value_list)
                values = f"({values})"
            else:
                values = ",".join(value_list)
            sql = f"insert {table_name} ({columns}) values {values}"
            self.curosr().execute(sql, bind_value)
            self.connection().commit()
            return True
        except Exception as e:
            print(e)
            # 发生错误时回滚
            self.connection().rollback()
        return False

    def query(self):
        self.re_connection()
        pass

    def curosr(self):
        return Container.instance("Connection").get_cursor()

    def refres_cursor(self):
        return Container.instance("Connection").refresh_cursor()

    def connection(self):
        return Container.instance("Connection").get_connection()
